﻿#region Librerias

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using ESF.General.Entity;
using ESF.SFT.Entities;
using ESF.General;
using System.IO;
using ESF.General.Text;
using System.Net.Mail;

#endregion


namespace ESF.SFT.DataAccess
{
	public static class AdoFileReceiveLog
	{
		#region Read

		public static Couple<string, string>[] GetFileUploadResumen(string pUserName)
		{
			Dictionary<string, string> result;

			DateTime todayStart;


			result = new Dictionary<string, string>();

			todayStart = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0);

			using (DBContext DB = new DBContext())
			{
				return new Couple<string, string>[]
				{
					new Couple<string, string> (
						"TotalFileCount", 
						(from R in DB.FileReceiveLog 
							where R.UploadUserAlias == pUserName
							select R).Count ().ToString ()),

					new Couple<string, string> (
						"TodayFileCount", 
						(from R in DB.FileReceiveLog
							where R.UploadUserAlias == pUserName 
							&& R.UploadDateTime >= todayStart
							&& R.UploadDateTime <= DateTime.Now
							select R).Count ().ToString ())
				};
			}
		}


		public static BrowseResults<FileReceiveLog> GetList(FileReceiveLog pSample, PaggingInfo pPagInfo)
		{
			var where = PredicateBuilder.True<FileReceiveLog>();


			using (DBContext DB = new DBContext())
			{
				where = where.AndIfNotNull(f => f.UploadUserAlias == pSample.UploadUserAlias, pSample.UploadUserAlias);
				where = where.AndIfNotNull(f => f.UploadDateTime >= pSample.filStartUploadDateTime, DataHelper.SetAsStartDate(pSample.filStartUploadDateTime));
				where = where.AndIfNotNull(f => f.UploadDateTime <= pSample.filEndUploadDateTime, DataHelper.SetAsEndDate(pSample.filEndUploadDateTime));

				return BrowseResults<FileReceiveLog>.GetInstanceWithOrderDesc<FileReceiveLog, DateTime?>(DB.FileReceiveLog, where, f => f.UploadDateTime, pPagInfo);
			}
		}


		public static string GetGUIDVersion(string pFileName)
		{
			IDbCommand cmd;

			string guid;

			
			cmd = DBContext.R.CreateCommand(
				@"SELECT 
					X.GUID
				FROM
					(SELECT 
						ROW_NUMBER () OVER (ORDER BY FRLV.UploadDateTime DESC, FRLV.ArrivalForDistributionDateTime DESC) i,
						FRLV.GUID
					FROM 
						FileReceiveLogVersions FRLV 
					INNER JOIN 
						FileReceiveLog FRL ON FRL.ID = FRLV.FileReceiveLogID
					WHERE 
						FRL.FileName LIKE @pFileName
				) X
				WHERE
					X.i = 1");

			DBContext.R.AddParameter(cmd, "pFileName", Path.GetFileName(pFileName), General.Text.ComparisonTypes.LikeRight);

			guid = DBContext.R.GetScalar<string>(cmd);
			
			return guid;
		}

		#endregion


		#region Write

		public static void Register(FileReceiveLog pFileReceiveLog)
		{
			long? existentLogID;

			IDbCommand cmd;


			using (DBContext DB = new DBContext())
			{
				existentLogID = (from FRL in DB.FileReceiveLog
								 where
									 FRL.FileName == pFileReceiveLog.FileName
									 && FRL.UploadUserAlias == pFileReceiveLog.UploadUserAlias
								 select
									 FRL.ID).FirstOrDefault();
			}

			//using (ContextSingleTransactionScope TS = DBContext.W.CreateContextSingleTransactionScope())
			//{			
			if (existentLogID != null)
			{
				cmd = DBContext.W.CreateCommand(
					@"UPDATE 
							FileReceiveLog 
						SET 
							UploadDateTime = @pUploadDateTime, 
							LengthBytes = @pLengthBytes, 
							ServerHostAddress = @pServerHostAddress, 
							ServerHostName = @pServerHostName,
							UserHostAddress = @pUserHostAddress, 
							MD5Hash = @pMD5Hash, 
							TryCount = TryCount + 1 
						WHERE 
							ID = @pID");

				DBContext.W.AddParameter(cmd, "pUploadDateTime", pFileReceiveLog.UploadDateTime);
				DBContext.W.AddParameter(cmd, "pLengthBytes", pFileReceiveLog.LengthBytes);
				DBContext.W.AddParameter(cmd, "pServerHostAddress", pFileReceiveLog.ServerHostAddress);
				DBContext.W.AddParameter(cmd, "pUserHostAddress", pFileReceiveLog.UserHostAddress);
				DBContext.W.AddParameter(cmd, "pServerHostName", pFileReceiveLog.ServerHostName);
				DBContext.W.AddParameter(cmd, "pMD5Hash", pFileReceiveLog.MD5Hash);
				DBContext.W.AddIDParameter(cmd, "pID", existentLogID);

				DBContext.W.Execute(cmd);
			}
			else
			{
				cmd = DBContext.W.CreateCommand(
					@"INSERT INTO 
							FileReceiveLog
							(FileName
							,UploadDateTime
							,UploadUserAlias
							,LengthBytes
							,ServerHostName
							,ServerHostAddress
							,UserHostAddress
							,IsDigitalSignInvalid
							,DigitalSignInvalidDetail
							,MD5Hash
							,ShortIdentificator
							,IdentificationNumber
							,IdentificationTypeCode
							,TryCount)
						VALUES
							(@pFileName
							,@pUploadDateTime
							,@pUploadUserAlias
							,@pLengthBytes
							,@pServerHostName
							,@pServerHostAddress
							,@pUserHostAddress
							,@pIsDigitalSignInvalid
							,@pDigitalSignInvalidDetail
							,@pMD5Hash
							,@pShortIdentificator
							,@pIdentificationNumber
							,@pIdentificationTypeCode
							,1);
							
						SELECT @@IDENTITY");

				DBContext.W.AddParameter(cmd, "pFileName", pFileReceiveLog.FileName);
				DBContext.W.AddParameter(cmd, "pUploadDateTime", pFileReceiveLog.UploadDateTime);
				DBContext.W.AddParameter(cmd, "pUploadUserAlias", pFileReceiveLog.UploadUserAlias);
				DBContext.W.AddParameter(cmd, "pLengthBytes", pFileReceiveLog.LengthBytes);
				DBContext.W.AddParameter(cmd, "pServerHostName", pFileReceiveLog.ServerHostName);
				DBContext.W.AddParameter(cmd, "pServerHostAddress", pFileReceiveLog.ServerHostAddress);
				DBContext.W.AddParameter(cmd, "pUserHostAddress", pFileReceiveLog.UserHostAddress);
				DBContext.W.AddParameter(cmd, "pIsDigitalSignInvalid", pFileReceiveLog.IsDigitalSignInvalid);
				DBContext.W.AddParameter(cmd, "pDigitalSignInvalidDetail", pFileReceiveLog.DigitalSignInvalidDetail);
				DBContext.W.AddParameter(cmd, "pMD5Hash", pFileReceiveLog.MD5Hash);
				DBContext.W.AddParameter(cmd, "pShortIdentificator", pFileReceiveLog.ShortIdentificator);
				DBContext.W.AddParameter(cmd, "pIdentificationNumber", pFileReceiveLog.IdentificationNumber);
				DBContext.W.AddParameter(cmd, "pIdentificationTypeCode", pFileReceiveLog.IdentificationTypeCode);


				existentLogID = (long)DBContext.W.ExecuteScalar<decimal>(cmd);
			}

			cmd = DBContext.W.CreateCommand(
				@"INSERT INTO [FileReceiveLogVersions]
						([FileReceiveLogID]
						,[GUID]
						,[MD5Hash]
						,[UploadDateTime]
						,[UploadUserAlias]
						,[LengthBytes]
						,[ServerHostName]
						,[ServerHostAddress]
						,[UserHostAddress]
						,[IsDigitalSignInvalid]
						,[DigitalSignInvalidDetail])
					VALUES
						(@pFileReceiveLogID
						,@pGUID
						,@pMD5Hash
						,@pUploadDateTime
						,@pUploadUserAlias
						,@pLengthBytes
						,@pServerHostName
						,@pServerHostAddress
						,@pUserHostAddress
						,@pIsDigitalSignInvalid
						,@pDigitalSignInvalidDetail)");

			DBContext.W.AddIDParameter(cmd, "pFileReceiveLogID", existentLogID);
			DBContext.W.AddParameter(cmd, "pGUID", pFileReceiveLog.GUID);
			DBContext.W.AddParameter(cmd, "pMD5Hash", pFileReceiveLog.MD5Hash);
			DBContext.W.AddParameter(cmd, "pUploadDateTime", pFileReceiveLog.UploadDateTime);
			DBContext.W.AddParameter(cmd, "pUploadUserAlias", pFileReceiveLog.UploadUserAlias);
			DBContext.W.AddParameter(cmd, "pLengthBytes", pFileReceiveLog.LengthBytes);
			DBContext.W.AddParameter(cmd, "pServerHostName", pFileReceiveLog.ServerHostName);
			DBContext.W.AddParameter(cmd, "pServerHostAddress", pFileReceiveLog.ServerHostAddress);
			DBContext.W.AddParameter(cmd, "pUserHostAddress", pFileReceiveLog.UserHostAddress);
			DBContext.W.AddParameter(cmd, "pIsDigitalSignInvalid", pFileReceiveLog.IsDigitalSignInvalid);
			DBContext.W.AddParameter(cmd, "pDigitalSignInvalidDetail", pFileReceiveLog.DigitalSignInvalidDetail);

			DBContext.W.Execute(cmd);


			//TS.Complete ();
			//}
		}



		public static void RegisterDigitalSignedFileMoved(string pGUID)
		{
			RegisterXNow(pGUID, "DigitalSignedFileMovedDateTime");
		}

		public static void RegisterDecompression(string pGUID)
		{
			RegisterXNow(pGUID, "DecompressionDateTime");
		}

		public static void RegisterArrivalForValidation(string pGUID)
		{
			RegisterXNow(pGUID, "ArrivalForValidationDateTime");
		}

		public static void RegisterCopyToReceived(string pGUID)
		{
			RegisterXNow(pGUID, "CopyToReceivedDateTime");
		}


		public static void RegisterCopyToApplication(string pGUID)
		{
			RegisterXNow(pGUID, "CopyToApplicationDateTime");
		}


		public static void RegisterArrivalForDistribution(string pGUID)
		{
			RegisterXNow(pGUID, "ArrivalForDistributionDateTime");
		}


		private static void RegisterXNow(string pGUID, string pDateTimeColumnName)
		{
			IDbCommand cmd;


			cmd = DBContext.W.CreateCommand(string.Format("UPDATE FileReceiveLogVersions SET {0} = GETDATE () WHERE GUID = @pGUID", pDateTimeColumnName));

			DBContext.W.AddParameter(cmd, "pGUID", pGUID);

			DBContext.W.Execute(cmd);
		}


		public static void RegisterStatus(string pGUID, bool pIsValid, bool pIsEnterpriseOK, MailMessage pNotification = null)
		{
			IDbCommand cmd;


			cmd = DBContext.W.CreateCommand("UPDATE FileReceiveLogVersions SET IsEnterpriseOK = @pIsEnterpriseOK, IsValid = @pIsValid WHERE GUID = @pGUID");

			DBContext.W.AddParameter(cmd, "pGUID", pGUID);
			DBContext.W.AddParameter(cmd, "pIsEnterpriseOK", pIsEnterpriseOK);
			DBContext.W.AddParameter(cmd, "pIsValid", pIsValid);

			DBContext.W.Execute(cmd);
		}


		public static void RegisterValidationResult(string pGUID, string pXMLValidationErrors)
		{
			IDbCommand cmd;


			cmd = DBContext.W.CreateCommand("UPDATE ValidationDateTime = GETDATE (), ValidationErrors = @pValidationErrors WHERE GUID = @pGUID");

			DBContext.W.AddParameter(cmd, "pGUID", pGUID);
			DBContext.W.AddParameter(cmd, "pValidationErrorsXML", pXMLValidationErrors);

			DBContext.W.Execute(cmd);
		}

		#endregion
	}
}
